package com.cpi.cornpp.monthsperiods.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.cpi.cornpp.common.exception.CornPPException;
import com.cpi.cornpp.monthsperiods.dto.MonthsPeriodsDTO;
import com.cpi.cornpp.monthsperiods.exception.MonthsPeriodsException;
import com.cpi.cornpp.monthsperiods.view.MonthsPeriodsForVersionDTO;
import com.cpi.cornpp.period.exception.PeriodException;
import com.cs.common.pool.DBDAO;
import com.cs.common.pool.exception.PoolException;

/**
 * 
 * @description: This Class is used to handle the Months Periods Data Access
 * @author Edgar Joao
 */
public class MonthsPeriodsDAO extends DBDAO{

	/** Datasource name*/
	private static final String DATASOURCE 	= "cornpp";//$NON-NLS-1$
	/** SQL List Months Periods */ 
	private static final String SQL_LIST_MONTHS_PERIODS =  "select month_period_id, short_name, description, month, year, short_name +' '+ convert(varchar, year) as list_description from months_periods";//$NON-NLS-1$
	/** SQL select actual period*/
	private static final String SQL_ACTUAL_PERIOD = "select top 1 month_period_id from months_periods where getdate() > convert( datetime, convert( varchar, year ) + '/' + convert( varchar, month ) + '/01' ) order by convert( datetime, convert( varchar, year ) + '/' + convert( varchar, month ) + '/01' ) desc";//$NON-NLS-1$
	/** SQL Periodos mensuales por orden de compra para version */
	private static final String SQL_LIST_MONTHS_PERIODS_FOR_PO = "select mp.month_period_id, mp.short_name, mp.description, mp.month, mp.year, mp.short_name +' '+ convert(varchar, mp.year) as list_description, month_period_version_id from months_periods_versions mpv inner join biweeks_periods_versions bpv on mpv.version_id = bpv.version_id inner join biweeks_periods bp on bp.biweek_period_id = bpv.biweek_period_id inner join purchases_orders_versions pov on pov.biweek_period_version_id = bpv.biweek_period_version_id and purchase_order_version_id = ? inner join months_periods mp on mpv.month_period_id = mp.month_period_id and convert( datetime, convert( varchar, mp.year ) + '/' + convert( varchar, mp.month ) + '/01' ) >= convert( datetime, convert( varchar, bp.year ) + '/' + convert( varchar, bp.month ) + '/01' ) where mpv.version_id = ?";//$NON-NLS-1$
	/** SQL Periodos mensuales por version */
	private static final String SQL_LIST_MONTHS_FOR_VERSION = "select mp.month_period_id, mp.short_name, mp.description, mp.month, mp.year, mp.short_name +' '+ convert(varchar, mp.year) as list_description, month_period_version_id from months_periods_versions mpv inner join months_periods mp on mpv.month_period_id = mp.month_period_id where mpv.version_id = ?" ;
	/** SQL Periodos mensuales para draft discount */
	private static final String SQL_LIST_MONTHS_PERIODS_DRAFT_DISCOUNT = "select months_periods.month_period_id, short_name, description, month, year, short_name +' '+ convert(varchar, year) as list_description, months_periods_versions.month_period_version_id from months_periods left join months_periods_versions on version_id = ? and months_periods.month_period_id = months_periods_versions.month_period_id where start_date >= (SELECT  convert( datetime, convert( varchar, month ) + '/01/' + convert( varchar, year ), 101 ) FROM purchases_orders_versions pov inner join biweeks_periods_versions bpv on pov.biweek_period_version_id = bpv.biweek_period_version_id inner join biweeks_periods bp on bp.biweek_period_id = bpv.biweek_period_id WHERE purchase_order_version_id = ? )order by start_date";//$NON-NLS-1$
	/**
	 * This method return a list of Months Periods to draft discount
	 * @author Jose Delgado Rguez
	 * @return ArrayList
	 * @throws MonthsPeriodsException
	 */
	public List monthsToDraftDiscount(int versionId, int purchaseOrderVersionId, int months) throws MonthsPeriodsException{
		List monthsToDraftDiscount = null;
		PreparedStatement preparedStatement =  null;
		ResultSet resutlSet = null;
		MonthsPeriodsForVersionDTO monthsPeriodsDTO = null;
		String strMonthPeriodVersionId = null;
		try{
			monthsToDraftDiscount = new ArrayList(0);
			preparedStatement = prepareSQL(SQL_LIST_MONTHS_PERIODS_DRAFT_DISCOUNT, DATASOURCE);
			preparedStatement.setInt(1, versionId);
			preparedStatement.setInt(2, purchaseOrderVersionId);
			resutlSet = executeQuery(preparedStatement);
			while (resutlSet.next() && months > 0){
				monthsPeriodsDTO = new MonthsPeriodsForVersionDTO();
				monthsPeriodsDTO.setMonthPeriodId(resutlSet.getInt(1));
				monthsPeriodsDTO.setShortName(resutlSet.getString(2));
				monthsPeriodsDTO.setDescription(resutlSet.getString(3));
				monthsPeriodsDTO.setMonth(resutlSet.getInt(4));
				monthsPeriodsDTO.setYear(resutlSet.getInt(5));
				monthsPeriodsDTO.setListDescription(resutlSet.getString(6));
				strMonthPeriodVersionId = resutlSet.getString(7);
				if(strMonthPeriodVersionId != null){
					monthsPeriodsDTO.setMonthPeriodVersionId(new Integer(strMonthPeriodVersionId));
				}
				monthsToDraftDiscount.add(monthsPeriodsDTO);
				months--;
			}
		}catch (PoolException poolException){
			MonthsPeriodsException monthsPeriodsException = null;
			poolException.printStackTrace(System.out);
			monthsPeriodsException= new MonthsPeriodsException(poolException , CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw monthsPeriodsException;
		}catch (SQLException sqlException){
			MonthsPeriodsException monthsPeriodsException = null;
			sqlException.printStackTrace(System.out);
			monthsPeriodsException= new MonthsPeriodsException(sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw monthsPeriodsException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){}
		}
		return monthsToDraftDiscount;
	}
	/**
	 * This method return a list of Months Periods
	 * @author Edgar Joao
	 * @return
	 * @throws Exception
	 */
	public List listOfMonthsPeriods( ) throws MonthsPeriodsException{
		ArrayList arrayResult = null;
		PreparedStatement preparedStatement =  null;
		MonthsPeriodsDTO monthsPeriodsDTO = null;
		
		try {
			arrayResult = new ArrayList(0);					
			preparedStatement =  prepareSQL(SQL_LIST_MONTHS_PERIODS, DATASOURCE);			
			ResultSet rst = executeQuery(preparedStatement);
			while (rst.next()){
				monthsPeriodsDTO = new MonthsPeriodsDTO();
				monthsPeriodsDTO.setMonthPeriodId(rst.getInt(1));
				monthsPeriodsDTO.setShortName(rst.getString(2));
				monthsPeriodsDTO.setDescription(rst.getString(3));
				monthsPeriodsDTO.setMonth(rst.getInt(4));
				monthsPeriodsDTO.setYear(rst.getInt(5));
				monthsPeriodsDTO.setListDescription(rst.getString(6));
				arrayResult.add(monthsPeriodsDTO);
			}
		}catch (PoolException poolException){
			MonthsPeriodsException monthsPeriodsException = null;
			poolException.printStackTrace(System.out);
			monthsPeriodsException= new MonthsPeriodsException(poolException , CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw monthsPeriodsException;
		}catch (SQLException sqlException){
			MonthsPeriodsException monthsPeriodsException = null;
			sqlException.printStackTrace(System.out);
			monthsPeriodsException= new MonthsPeriodsException(sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw monthsPeriodsException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return arrayResult;
	}/**
	 * Obtiene el id del periodo actual 
	 * @return id periodo
	 * @throws PeriodException
	 */
	public int actualPeriod()throws MonthsPeriodsException{
		PreparedStatement preparedStatement =  null;
		ResultSet rst = null;
		int actualPeriod = 0;
		try{
			preparedStatement =  prepareSQL(SQL_ACTUAL_PERIOD, DATASOURCE);
			rst = executeQuery(preparedStatement);
			if (rst.next()){
				actualPeriod = rst.getInt(1);
			}
		}catch (PoolException poolException){
			MonthsPeriodsException monthsPeriodsException = null;
			poolException.printStackTrace(System.out);
			monthsPeriodsException = new MonthsPeriodsException (poolException, CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			throw monthsPeriodsException;
		} catch(SQLException sqle){
			MonthsPeriodsException monthsPeriodsException = null;
			sqle.printStackTrace(System.out);
			monthsPeriodsException = new MonthsPeriodsException (sqle, CornPPException.LAYER_DAO,CornPPException.ACTION_SELECT);
			throw monthsPeriodsException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return actualPeriod;
	}
	
	public List listForVersion(int versionId, int purchaseOrderVersionId)throws MonthsPeriodsException{
		ArrayList arrayResult = null;
		PreparedStatement preparedStatement =  null;
		MonthsPeriodsForVersionDTO monthsPeriodsForVersionDTO = null;
		
		try {
			arrayResult = new ArrayList(0);					
			preparedStatement =  prepareSQL(SQL_LIST_MONTHS_PERIODS_FOR_PO, DATASOURCE);
			preparedStatement.setInt(1, purchaseOrderVersionId);
			preparedStatement.setInt(2, versionId);
			ResultSet rst = executeQuery(preparedStatement);
			while (rst.next()){
				monthsPeriodsForVersionDTO = new MonthsPeriodsForVersionDTO();
				monthsPeriodsForVersionDTO.setMonthPeriodId(rst.getInt(1));
				monthsPeriodsForVersionDTO.setShortName(rst.getString(2));
				monthsPeriodsForVersionDTO.setDescription(rst.getString(3));
				monthsPeriodsForVersionDTO.setMonth(rst.getInt(4));
				monthsPeriodsForVersionDTO.setYear(rst.getInt(5));
				monthsPeriodsForVersionDTO.setListDescription(rst.getString(6));
				monthsPeriodsForVersionDTO.setMonthPeriodVersionId(new Integer(rst.getInt(7)));
				arrayResult.add(monthsPeriodsForVersionDTO);
			}
		}catch (PoolException poolException){
			MonthsPeriodsException monthsPeriodsException = null;
			poolException.printStackTrace(System.out);
			monthsPeriodsException= new MonthsPeriodsException(poolException , CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw monthsPeriodsException;
		}catch (SQLException sqlException){
			MonthsPeriodsException monthsPeriodsException = null;
			sqlException.printStackTrace(System.out);
			monthsPeriodsException= new MonthsPeriodsException(sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw monthsPeriodsException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return arrayResult;
	}
	
	public List listForVersion(int versionId)throws MonthsPeriodsException{
		ArrayList arrayResult = null;
		PreparedStatement preparedStatement =  null;
		MonthsPeriodsForVersionDTO monthsPeriodsForVersionDTO = null;
		
		try {
			arrayResult = new ArrayList(0);					
			preparedStatement =  prepareSQL(SQL_LIST_MONTHS_FOR_VERSION, DATASOURCE);
			preparedStatement.setInt(1, versionId);
			ResultSet rst = executeQuery(preparedStatement);
			while (rst.next()){
				monthsPeriodsForVersionDTO = new MonthsPeriodsForVersionDTO();
				monthsPeriodsForVersionDTO.setMonthPeriodId(rst.getInt(1));
				monthsPeriodsForVersionDTO.setShortName(rst.getString(2));
				monthsPeriodsForVersionDTO.setDescription(rst.getString(3));
				monthsPeriodsForVersionDTO.setMonth(rst.getInt(4));
				monthsPeriodsForVersionDTO.setYear(rst.getInt(5));
				monthsPeriodsForVersionDTO.setListDescription(rst.getString(6));
				monthsPeriodsForVersionDTO.setMonthPeriodVersionId(new Integer(rst.getInt(7)));
				arrayResult.add(monthsPeriodsForVersionDTO);
			}
		}catch (PoolException poolException){
			MonthsPeriodsException monthsPeriodsException = null;
			poolException.printStackTrace(System.out);
			monthsPeriodsException= new MonthsPeriodsException(poolException , CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw monthsPeriodsException;
		}catch (SQLException sqlException){
			MonthsPeriodsException monthsPeriodsException = null;
			sqlException.printStackTrace(System.out);
			monthsPeriodsException= new MonthsPeriodsException(sqlException , CornPPException.LAYER_DAO,CornPPException.ACTION_LISTS);
			throw monthsPeriodsException;
		}finally {
			try{
				closeConnection(preparedStatement.getConnection());
			}catch(Exception exception){
			}
		}
		return arrayResult;
	}
}